ALTER PROC RedBalTest
AS
BEGIN

DECLARE @month INT = 5, @year INT = 2012

SELECT 
	l.loan_no AS [Loan No],
	l.product AS [Product],
	CONVERT(VARCHAR(9), l.start_date, 6) AS [Start Date],
	CONVERT(VARCHAR(9), l.end_date, 6) AS [End Date],
	l.principal AS [Amount],
	l.term AS [Term],
	l.period AS [Frequency],
	l.interest_rate * 100 AS [Rate (%)],	
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month, @year, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month, @year, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month, @year, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [May 2012],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 1, @year, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 1, @year, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 1, @year, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [June 2012],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 2, @year, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 2, @year, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 2, @year, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [July 2012],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 3, @year , L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 3, @year, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 3, @year, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [August 2012],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 4, @year, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 4, @year, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 4, @year, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [September 2012],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 5, @year, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 5, @year, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 5, @year, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [October 2012],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 6, @year, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 6, @year, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 6, @year, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [November 2012],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 7, @year, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 7, @year, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 7, @year, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [December 2012],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month - 4, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month - 4, @year + 1, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month - 4, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [January 2013],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month - 3, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month - 3, @year + 1, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month - 3, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [February 2013],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month - 2, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month - 2, @year + 1, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month - 2, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [March 2013],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month - 1, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month - 1, @year + 1, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month - 1, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [April 2013],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month, @year + 1, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [May 2013],	
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 1, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 1, @year + 1, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 1, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [June 2013],				
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 2, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 2, @year + 1, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 2, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [July 2013],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 3, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 3, @year + 1, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 3, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [August 2013],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 4, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 4, @year + 1, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 4, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [September 2013],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 5, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 5, @year + 1, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 5, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [October 2013],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 6, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 6, @year + 1, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 6, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [November 2013],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 7, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 7, @year + 1, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 7, @year + 1, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [December 2013],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month - 4, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month - 4, @year + 2, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month - 4, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [January 2014],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month - 3, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month - 3, @year + 2, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month - 3, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [February 2014],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month - 2, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month - 2, @year + 2, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month - 2, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [March 2014],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month - 1, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month - 1, @year + 2, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month - 1, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [April 2014],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month , @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month, @year + 2, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [May 2014],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 1, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 1, @year + 2, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 1, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [June 2014],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 2, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 2, @year + 2, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 2, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [July 2014],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 3, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 3, @year + 2, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 3, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [August 2014],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month  + 4, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 4, @year + 2, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 4, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [September 2014],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 5, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 5, @year + 2, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 5, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [October 2014],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 6, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 6, @year + 2, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 6, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [November 2014],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month + 7, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month + 7, @year + 2, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month + 7, @year + 2, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [December 2014],
	CASE 
		WHEN L.period = 'WEEKLY' 
			THEN dbo.CalInterestWkly(@month - 4, @year + 3, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
		WHEN L.period = 'BI-MONTHLY'
			THEN dbo.CalInterestBiMnthly(@month - 4, @year + 3, L.principal, L.interest_rate * 100 , L.start_date, L.term, L.installment)
		WHEN L.period = 'MONTHLY' 
			THEN dbo.CalInterestMnthly(@month - 4, @year + 3, L.principal, L.interest_rate * 100, L.start_date, L.term, L.installment)
	END
	AS [January 2015]
FROM 
	loan_rbm l
ORDER BY
	l.start_date
END

--exec dbo.RedBalTest